const { pool } = require('../config/database');

class Department {
  // 获取所有部门
  static async findAll(page = 1, limit = 10, search = '') {
    try {
      const offset = (page - 1) * limit;
      let query = `
        SELECT d.*, p.name as parent_name
        FROM departments d
        LEFT JOIN departments p ON d.parent_id = p.id
      `;
      
      const params = [];
      if (search) {
        query += ` WHERE d.name LIKE ? OR d.code LIKE ? OR d.description LIKE ?`;
        params.push(`%${search}%`, `%${search}%`, `%${search}%`);
      }
      
      query += ` ORDER BY d.create_time DESC LIMIT ? OFFSET ?`;
      params.push(limit, offset);
      
      const [rows] = await pool.execute(query, params);
      
      // 获取总数
      let countQuery = 'SELECT COUNT(*) as total FROM departments';
      if (search) {
        countQuery += ` WHERE name LIKE ? OR code LIKE ? OR description LIKE ?`;
      }
      const [countResult] = await pool.execute(countQuery, search ? [`%${search}%`, `%${search}%`, `%${search}%`] : []);
      
      return {
        departments: rows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total: countResult[0].total,
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(`获取部门列表失败: ${error.message}`);
    }
  }

  // 根据ID获取部门
  static async findById(id) {
    try {
      const [rows] = await pool.execute(`
        SELECT d.*, p.name as parent_name
        FROM departments d
        LEFT JOIN departments p ON d.parent_id = p.id
        WHERE d.id = ?
      `, [id]);
      
      return rows[0] || null;
    } catch (error) {
      throw new Error(`获取部门失败: ${error.message}`);
    }
  }

  // 根据代码获取部门
  static async findByCode(code) {
    try {
      const [rows] = await pool.execute('SELECT * FROM departments WHERE code = ?', [code]);
      return rows[0] || null;
    } catch (error) {
      throw new Error(`根据代码获取部门失败: ${error.message}`);
    }
  }

  // 获取所有部门（不分页，用于下拉选择）
  static async getAllDepartments() {
    try {
      const [rows] = await pool.execute('SELECT id, name, code, parent_id FROM departments ORDER BY name');
      return rows;
    } catch (error) {
      throw new Error(`获取所有部门失败: ${error.message}`);
    }
  }

  // 获取部门树结构
  static async getDepartmentTree() {
    try {
      const [rows] = await pool.execute(`
        SELECT d.*, p.name as parent_name,
               (SELECT COUNT(*) FROM users WHERE department_id = d.id) as user_count
        FROM departments d
        LEFT JOIN departments p ON d.parent_id = p.id
        ORDER BY d.parent_id, d.name
      `);
      
      // 构建树结构
      const buildTree = (items, parentId = null) => {
        return items
          .filter(item => item.parent_id === parentId)
          .map(item => ({
            ...item,
            children: buildTree(items, item.id)
          }));
      };
      
      return buildTree(rows);
    } catch (error) {
      throw new Error(`获取部门树失败: ${error.message}`);
    }
  }

  // 创建部门
  static async create(departmentData) {
    try {
      const { name, code, parent_id, leader, description } = departmentData;
      
      // 检查部门代码是否已存在
      const existingDepartment = await this.findByCode(code);
      if (existingDepartment) {
        throw new Error('部门代码已存在');
      }
      
      // 如果指定了父部门，检查父部门是否存在
      if (parent_id) {
        const parentDepartment = await this.findById(parent_id);
        if (!parentDepartment) {
          throw new Error('父部门不存在');
        }
      }
      
      const [result] = await pool.execute(`
        INSERT INTO departments (name, code, parent_id, leader, description)
        VALUES (?, ?, ?, ?, ?)
      `, [name, code, parent_id, leader, description]);
      
      return { id: result.insertId, ...departmentData };
    } catch (error) {
      throw new Error(`创建部门失败: ${error.message}`);
    }
  }

  // 更新部门
  static async update(id, departmentData) {
    try {
      const { name, code, parent_id, leader, description } = departmentData;
      
      // 检查部门是否存在
      const existingDepartment = await this.findById(id);
      if (!existingDepartment) {
        throw new Error('部门不存在');
      }
      
      // 如果更新部门代码，检查是否与其他部门冲突
      if (code && code !== existingDepartment.code) {
        const departmentWithSameCode = await this.findByCode(code);
        if (departmentWithSameCode) {
          throw new Error('部门代码已存在');
        }
      }
      
      // 如果指定了父部门，检查父部门是否存在且不能是自己
      if (parent_id) {
        if (parseInt(parent_id) === parseInt(id)) {
          throw new Error('部门不能设置自己为父部门');
        }
        const parentDepartment = await this.findById(parent_id);
        if (!parentDepartment) {
          throw new Error('父部门不存在');
        }
      }
      
      const [result] = await pool.execute(`
        UPDATE departments 
        SET name = ?, code = ?, parent_id = ?, leader = ?, description = ?
        WHERE id = ?
      `, [name, code, parent_id, leader, description, id]);
      
      if (result.affectedRows === 0) {
        throw new Error('更新失败，部门可能不存在');
      }
      
      return await this.findById(id);
    } catch (error) {
      throw new Error(`更新部门失败: ${error.message}`);
    }
  }

  // 删除部门
  static async delete(id) {
    try {
      // 检查是否有用户使用此部门
      const [users] = await pool.execute('SELECT COUNT(*) as count FROM users WHERE department_id = ?', [id]);
      if (users[0].count > 0) {
        throw new Error('无法删除部门，有用户正在使用此部门');
      }
      
      // 检查是否有子部门
      const [children] = await pool.execute('SELECT COUNT(*) as count FROM departments WHERE parent_id = ?', [id]);
      if (children[0].count > 0) {
        throw new Error('无法删除部门，存在子部门');
      }
      
      const [result] = await pool.execute('DELETE FROM departments WHERE id = ?', [id]);
      
      if (result.affectedRows === 0) {
        throw new Error('删除失败，部门可能不存在');
      }
      
      return true;
    } catch (error) {
      throw new Error(`删除部门失败: ${error.message}`);
    }
  }

  // 批量删除部门
  static async deleteMultiple(ids) {
    try {
      // 检查是否有用户使用这些部门
      const placeholders = ids.map(() => '?').join(',');
      const [users] = await pool.execute(`SELECT COUNT(*) as count FROM users WHERE department_id IN (${placeholders})`, ids);
      if (users[0].count > 0) {
        throw new Error('无法删除部门，有用户正在使用这些部门');
      }
      
      // 检查是否有子部门
      const [children] = await pool.execute(`SELECT COUNT(*) as count FROM departments WHERE parent_id IN (${placeholders})`, ids);
      if (children[0].count > 0) {
        throw new Error('无法删除部门，存在子部门');
      }
      
      const [result] = await pool.execute(`DELETE FROM departments WHERE id IN (${placeholders})`, ids);
      
      return result.affectedRows;
    } catch (error) {
      throw new Error(`批量删除部门失败: ${error.message}`);
    }
  }

  // 获取部门下的用户
  static async getUsersByDepartment(departmentId, page = 1, limit = 10) {
    try {
      const offset = (page - 1) * limit;
      
      const [rows] = await pool.execute(`
        SELECT u.*, r.name as role_name
        FROM users u
        LEFT JOIN roles r ON u.role_id = r.id
        WHERE u.department_id = ?
        ORDER BY u.create_time DESC
        LIMIT ? OFFSET ?
      `, [departmentId, limit, offset]);
      
      const [countResult] = await pool.execute(
        'SELECT COUNT(*) as total FROM users WHERE department_id = ?',
        [departmentId]
      );
      
      return {
        users: rows,
        pagination: {
          page: parseInt(page),
          limit: parseInt(limit),
          total: countResult[0].total,
          totalPages: Math.ceil(countResult[0].total / limit)
        }
      };
    } catch (error) {
      throw new Error(`获取部门用户失败: ${error.message}`);
    }
  }

  // 获取部门统计信息
  static async getStats() {
    try {
      const [totalDepartments] = await pool.execute('SELECT COUNT(*) as total FROM departments');
      const [departmentsWithUsers] = await pool.execute(`
        SELECT COUNT(DISTINCT d.id) as with_users 
        FROM departments d 
        INNER JOIN users u ON d.id = u.department_id
      `);
      const [topLevelDepartments] = await pool.execute('SELECT COUNT(*) as top_level FROM departments WHERE parent_id IS NULL');
      
      return {
        total: totalDepartments[0].total,
        withUsers: departmentsWithUsers[0].with_users,
        topLevel: topLevelDepartments[0].top_level
      };
    } catch (error) {
      throw new Error(`获取部门统计失败: ${error.message}`);
    }
  }
}

module.exports = Department; 